﻿Public Class ClsDbAccessMdb
    '*******************************
    '* 数据库存取类 - mdb数据库
    '* 提供最基本的增 删 改查
    '*******************************
    '数据库连接对象
    Private mConnection As System.Data.OleDb.OleDbConnection  'SqlClient.SqlConnection
    '数据库连接字符串
    Private mConnectionString As String
    '当前连接是否正在事务中
    Private mInTransaction As Boolean
    '保存开启的事务
    Private mCurrentTransaction As System.Data.OleDb.OleDbTransaction

    Public Sub New()
        ' 如果存在
    End Sub

    ''' <summary>
    ''' 类构造函数
    ''' </summary>
    ''' <param name="mConnString">数据库连接字符串</param>
    Public Sub New(ByVal mConnString As String)
        If String.IsNullOrEmpty(mConnString) Then
            Exit Sub
        End If

        '将值直接赋值给mConnectionString
        mConnectionString = mConnString

        '判断当前的mConnection是否存在,如果不存在，则创建
        If mConnection Is Nothing Then
            Try
                '创建sqlConnection对象
                mConnection = New System.Data.OleDb.OleDbConnection(mConnectionString) 'New SqlClient.SqlConnection(mConnectionString)
            Catch ex As System.Data.OleDb.OleDbException  'SqlClient.SqlException  'Exception
                modError.mErrorNumber = ex.ErrorCode 'ex.Number
                modError.mErrorMessage = ex.Message
                modError.mErrorCreater = "clsDbAccessMdb-New"
            End Try

        End If
    End Sub

    ''' <summary>
    ''' 属性：数据库连接字符串
    ''' </summary>
    ''' <returns>连接字符串</returns>
    Public Property ConnectionString As String
        Get
            Return mConnectionString
        End Get
        Set(value As String)
            If Not String.IsNullOrEmpty(value) Then
                mConnectionString = value
            End If
        End Set
    End Property

    Public Property InTransction As Boolean
        Get
            Return mInTransaction
        End Get
        Set(value As Boolean)
            mInTransaction = value
        End Set
    End Property

    Public ReadOnly Property ErrorNumber As Integer

        Get
            Return mErrorNumber
        End Get

    End Property

    Public ReadOnly Property ErrorMessage As String
        Get
            Return mErrorMessage
        End Get
    End Property

    Public ReadOnly Property ErrorCreater As String
        Get
            Return mErrorCreater
        End Get

    End Property

    ''' <summary>
    ''' 打开连接指定的数据库
    ''' </summary>
    ''' <returns>失败返回false</returns>
    Public Function DbOpen() As Boolean


        '判断当前链接对象是否存在
        If mConnection Is Nothing Then
            '判断是否连接字串是否为空
            If String.IsNullOrEmpty(mConnectionString) Then
                '抛出异常，退出
                mErrorNumber = -1
                mErrorMessage = "数据库连接字符串为空。"
                mErrorCreater = "clsDbAccessMdb - DbOpen"
                Return False
            End If
            '连接字符串存在，则生成连接对象
            Try
                mConnection = New System.Data.OleDb.OleDbConnection(mConnectionString) 'New SqlClient.SqlConnection(mConnectionString)
            Catch ex As System.Data.OleDb.OleDbException  'System.Data.SqlClient.SqlException
                mErrorNumber = ex.ErrorCode
                mErrorMessage = ex.Message
                mErrorCreater = "clsDbAccessMdb - DbOpen"
                Return False
            End Try
        End If

        '判断是否在事务中
        If mInTransaction Then
            '判断是否需要打开
            If mConnection.State <> ConnectionState.Open Then
                mConnection.Open()
            End If

            Return True
        End If

        '打开指定数据库
        Try
            mConnection.Open()
            Return True
        Catch ex As System.Data.OleDb.OleDbException
            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "clsDbAccessMdb - DbOpen"
            Return False
        End Try

    End Function

    ''' <summary>
    ''' 关闭数据库
    ''' </summary>
    Public Sub DbClose()

        '判断连接对象是否存在
        If mConnection IsNot Nothing Then
            Try
                '如果在事务中，退出
                If mInTransaction Then
                    Return
                End If

                mConnection.Close()
            Catch ex As System.Data.OleDb.OleDbException
                mErrorNumber = ex.ErrorCode
                mErrorMessage = ex.Message
                mErrorCreater = "clsDbAccessMdb - DbClose"
            End Try

        End If
    End Sub

    ''' <summary>
    ''' 更新数据表函数
    ''' </summary>
    ''' <param name="updateSql">更新sql语句，包含点位符{0}</param>
    ''' <param name="updateParameter">更新字段值的参数数组</param>
    ''' <returns>成功返回非0值</returns>
    Public Function UpdateRecord(ByVal updateSql As String, ByVal updateParameter() As OleDb.OleDbParameter) As Integer

        Dim mCmd As System.Data.OleDb.OleDbCommand
        Dim tmpRetInt As Integer

        Try
            '到此，可以创建查询对象
            mCmd = mConnection.CreateCommand()
            mCmd.CommandType = CommandType.Text
            mCmd.CommandText = updateSql
            mCmd.Parameters.AddRange(updateParameter)
            'For i = 0 To updateParameter.Length - 1
            '    mCmd.Parameters.Add(updateParameter(i))
            'Next
            If mInTransaction Then
                mCmd.Transaction = mCurrentTransaction
            End If

            tmpRetInt = mCmd.ExecuteNonQuery()
            mCmd.Parameters.Clear()

        Catch ex As System.Data.OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "clsDbAccessMdb - DbClose"
            '发生数据库错误时，返回-1
            tmpRetInt = -1
        End Try

        Return tmpRetInt

    End Function

    Public Function InsertData(ByVal sqlString As String, ByVal updateParameter() As OleDb.OleDbParameter, Optional ByVal ifReturnAutoId As Boolean = False) As Integer
        Dim mCmd As System.Data.OleDb.OleDbCommand  'System.Data.SqlClient.SqlCommand
        '判断连接对象是否存在
        Dim tmpRetInt As Integer

        If mConnection Is Nothing Then
            '如果连接对象不存在

            If Not DbOpen() Then
                Return 0
            End If

        Else

            '连接对象存在，则判断当前状态是否打开
            If mConnection.State <> System.Data.ConnectionState.Open Then '不是打开状态，则重新打开
                If Not DbOpen() Then
                    Return 0
                End If
            End If

        End If

        Try
            '到此，可以创建查询对象
            mCmd = mConnection.CreateCommand()
            mCmd.CommandText = sqlString
            mCmd.CommandType = CommandType.Text
            mCmd.Parameters.Clear()
            mCmd.Parameters.AddRange(updateParameter)
            'For i = 0 To updateParameter.Length - 1
            '    mCmd.Parameters.Add(updateParameter(i))
            'Next

            If mInTransaction Then
                mCmd.Transaction = mCurrentTransaction
            End If

            tmpRetInt = mCmd.ExecuteNonQuery()

            mCmd.Parameters.Clear()

            If ifReturnAutoId Then
                '再次运行查询，取出新创建的autoId
                'Dim sqlAutoId As String = 
                mCmd.CommandText = "SELECT @@IDENTITY as newId "
                Dim retObj = mCmd.ExecuteScalar()

                tmpRetInt = CType(retObj, Int32) 'CType(mCmd.ExecuteScalar(), Int32)
            End If


        Catch ex As System.Data.OleDb.OleDbException
            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "clsDbAccessMdb - DbClose"
            'Debug.Print(ex.Message)

            '发生数据库错误时，返回-1
            tmpRetInt = -1

        End Try

        Return tmpRetInt

    End Function


    ''' <summary>
    ''' 执行sql语句，无数据返回，仅返回执行状态
    '''  主要用于执行 创建数据表，删除数据，插入
    '''  数据等用途，不要用于执行select语句
    ''' </summary>
    ''' <param name="sqlString">要执行的插入删除等语句</param>
    ''' <returns>0表示失败</returns>
    Public Function ExecuteSqlNoDataReturn(ByVal sqlString As String, ByVal exeParas() As OleDb.OleDbParameter) As Integer

        Dim mCmd As System.Data.OleDb.OleDbCommand  'System.Data.SqlClient.SqlCommand

        Dim tmpRetInt As Integer

        '判断连接对象是否存在

        If mConnection Is Nothing Then
            '如果连接对象不存在

            If Not DbOpen() Then
                Return 0
            End If

        Else

            '连接对象存在，则判断当前状态是否打开
            If mConnection.State <> System.Data.ConnectionState.Open Then '不是打开状态，则重新打开
                If Not DbOpen() Then
                    Return 0
                End If
            End If

        End If

        Try
            '到此，可以创建查询对象
            mCmd = mConnection.CreateCommand()
            mCmd.CommandText = sqlString
            mCmd.CommandType = CommandType.Text

            mCmd.Parameters.AddRange(exeParas)

            If mInTransaction Then
                mCmd.Transaction = mCurrentTransaction
            End If

            tmpRetInt = mCmd.ExecuteNonQuery()

            mCmd.Parameters.Clear()

        Catch ex As System.Data.OleDb.OleDbException
            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "clsDbAccessMdb - DbClose"

            '发生数据库错误时，返回-1
            tmpRetInt = -1

        End Try

        Return tmpRetInt

    End Function

    Public Function ExecuteSqlReturnDataSet(ByVal sqlStr As String, ByVal paras() As OleDb.OleDbParameter) As OleDb.OleDbDataReader
        Dim mCmd As System.Data.OleDb.OleDbCommand
        Dim tmpRetReader As OleDb.OleDbDataReader = Nothing

        Try
            '到此，可以创建查询对象
            mCmd = mConnection.CreateCommand()

            mCmd.CommandText = sqlStr
            mCmd.CommandType = CommandType.Text
            mCmd.Parameters.AddRange(paras)

            If mInTransaction Then
                mCmd.Transaction = mCurrentTransaction
            End If

            tmpRetReader = mCmd.ExecuteReader()

            mCmd.Parameters.Clear()


        Catch ex As System.Data.OleDb.OleDbException
            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "clsDbAccessMdb - ExecuteSqlReturnDataSet"

        End Try

        Return tmpRetReader

    End Function

    ''' <summary>
    ''' 返回OleDbDataReader查询
    ''' </summary>
    ''' <param name="sqlString">查询语句</param>
    ''' <returns></returns>
    Public Function ExecuteSqlReturnDataSet(ByVal sqlString As String) As System.Data.OleDb.OleDbDataReader

        Dim mCmd As System.Data.OleDb.OleDbCommand

        Try
            '到此，可以创建查询对象
            mCmd = mConnection.CreateCommand()

            mCmd.CommandText = sqlString
            mCmd.CommandType = CommandType.Text

            If mInTransaction Then
                mCmd.Transaction = mCurrentTransaction
            End If

            Return mCmd.ExecuteReader()

        Catch ex As System.Data.OleDb.OleDbException
            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "clsDbAccessMdb - DbClose"

            Return Nothing
        End Try

    End Function

    ''' <summary>
    ''' 返回查询的第一行第一列数据
    ''' </summary>
    ''' <param name="sqlString">查询语句</param>
    ''' <returns></returns>
    Public Function ExecuteSqlReturnUniqueField(ByVal sqlString As String, ByVal sqlParameters() As OleDb.OleDbParameter) As Object
        Dim mCmd As System.Data.OleDb.OleDbCommand
        Dim tmpRetObj As Object = Nothing
        Try
            '到此，可以创建查询对象
            mCmd = mConnection.CreateCommand()

            mCmd.CommandText = sqlString
            mCmd.CommandType = CommandType.Text

            If sqlParameters IsNot Nothing Then
                mCmd.Parameters.AddRange(sqlParameters)
                'For Each p As OleDb.OleDbParameter In sqlParameters
                '    mCmd.Parameters.Add(p)
                'Next
            End If

            If mInTransaction Then
                mCmd.Transaction = mCurrentTransaction
            End If

            tmpRetObj = mCmd.ExecuteScalar()

            mCmd.Parameters.Clear()

        Catch ex As System.Data.OleDb.OleDbException
            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "clsDbAccessMdb - DbClose"

        Catch ex As Exception
            mErrorNumber = -1004
            mErrorMessage = ex.Message
            mErrorCreater = "clsDbAccessMdb - DbClose"
        End Try

        Return tmpRetObj

    End Function

    ''' <summary>
    ''' 开启数据处理事务
    ''' </summary>
    ''' <returns></returns>
    Public Function BeginTransaction() As Boolean

        If mConnection.State <> ConnectionState.Open Then
            'mConnection.Open()
            DbOpen()
        End If

        If Not mInTransaction Then
            mCurrentTransaction = mConnection.BeginTransaction()
            mInTransaction = True
        End If

        Return mInTransaction

    End Function

    ''' <summary>
    ''' 提交事务
    ''' </summary>
    ''' <returns>失败返回false</returns>
    Public Function CompleteTransaction() As Boolean
        Try
            If mInTransaction Then
                mCurrentTransaction.Commit()
                mInTransaction = False
                '关闭
                If mConnection.State = ConnectionState.Open Then

                    DbClose()
                End If
                Return True
            Else
                Return False
            End If
        Catch ex As System.Data.OleDb.OleDbException
            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "clsDbAccessMdb - DbClose"

            Return False
        End Try

    End Function

    ''' <summary>
    ''' 回滚当前事务
    ''' </summary>
    ''' <returns>失败返回false</returns>
    Public Function RollBackTransaction() As Boolean
        Try
            If mInTransaction Then
                mCurrentTransaction.Rollback()
                mInTransaction = False
                '如果库是打开状态，则关闭
                If mConnection.State = ConnectionState.Open Then
                    'mConnection.Close()
                    DbClose()
                End If

                Return True
            Else
                Return False
            End If
        Catch ex As System.Data.OleDb.OleDbException
            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "clsDbAccessMdb - DbClose"

            Return False
        End Try

    End Function

    ''' <summary>
    ''' 测试是否存在指定名称的表名
    ''' </summary>
    ''' <param name="tableName">要检测的表名</param>
    ''' <returns>T/F</returns>
    Public Function IfExistTable(ByVal tableName As String) As Boolean
        Dim tmpTableName As String = tableName.Trim()
        Dim tmpTable As DataTable
        Dim returnA As Boolean

        returnA = False

        If String.IsNullOrEmpty(tmpTableName) Then
            Return returnA
        End If

        Try
            tmpTable = mConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, New Object() {vbNull, vbNull, vbNull, "TABLE"})
            For i As Integer = 0 To tmpTable.Rows.Count - 1
                If tmpTable.Rows(i).ItemArray(2).ToString = tmpTableName Then
                    returnA = True
                    Exit For
                End If
            Next

            Return returnA

        Catch ex As System.Data.OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "clsDbAccessMdb - IfExistTable"

            Return False
        End Try

    End Function

End Class
